/* Script para la carga de Funciones, carga de tablas faltantes y actualización de existentes */
/* http://code.google.com/p/imagenesdb-gad/source/browse/ */

/*Campos geometricos*/

SELECT addgeometrycolumn('objeto','geom',0,'POLYGON',2);
SELECT addgeometrycolumn('objetoconsulta','geom',0,'POLYGON',2);

/*Tablas auxiliares*/

CREATE TABLE firma
(
  "idObjeto" bigint NOT NULL,
   CONSTRAINT "idFirma" PRIMARY KEY ("idObjeto")
);

CREATE TABLE pivote
(
  orden integer NOT NULL,
  "idObjeto" bigint,
  CONSTRAINT idpivote PRIMARY KEY (orden)
);

CREATE TABLE resultado
(
  idimagen bigint,
  distancia real,
  cantobjeto integer
);

CREATE TABLE resultadoparcial
(
  idimagen bigint,
  idobjeto bigint,
  distancia real
);

CREATE TABLE tiempo
(
  tiempo bigint
);

/*Modificacion de tablas*/

ALTER TABLE imagen
	ALTER COLUMN codxml TYPE text,
	ALTER COLUMN nombre TYPE text;

ALTER TABLE imagenconsulta
	ALTER COLUMN codxml TYPE text,
	ALTER COLUMN nombre TYPE text;

ALTER TABLE objeto
	ALTER COLUMN xmlpath TYPE text;

ALTER TABLE objetoconsulta
	ALTER COLUMN xmlpath TYPE text;

/*Funciones para el indice*/

CREATE OR REPLACE FUNCTION gad_crear_geom (id_obj bigint)
RETURNS VOID AS
$$
DECLARE
	g text;
	pto punto;
	aux punto;
	poligono geometry;
	poligono_nuevo geometry;
	escala real;
	mbb geometry;
	centro geometry;
	area double precision;
BEGIN
	g = 'POLYGON((';
	SELECT * INTO aux FROM punto WHERE objeto_id = id_obj ORDER BY id ASC LIMIT 1;
	FOR pto IN (SELECT * FROM punto WHERE objeto_id = id_obj) LOOP
		g = CONCAT(g, pto.x, ' ', pto.y, ',');
	END LOOP;
	g = CONCAT(g, aux.x, ' ', aux.y, '))');
	UPDATE objeto SET geom = st_geometryfromtext(g,0) WHERE id = id_obj;

	SELECT st_area(geom) INTO area FROM objeto WHERE id = id_obj;
	IF area > 0 THEN
		SELECT sqrt(200 / st_area(geom)) INTO escala FROM objeto WHERE id = id_obj;
		SELECT st_scale(geom, escala, escala) INTO poligono FROM objeto WHERE id = id_obj;
		SELECT st_centroid(box2d(poligono)) INTO centro;
		UPDATE objeto SET geom = st_translate(poligono,0 - st_x(centro),0 - st_y(centro)) WHERE id = id_obj;
	END IF;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION gad_distancia(id_obj_1 bigint, id_obj_2 bigint)
RETURNS real AS
$$
DECLARE
	distancia real;
BEGIN
	IF id_obj_1 = id_obj_2 THEN
		RETURN 0;
	ELSE
		SELECT st_area(st_intersection(obj_1.geom, obj_2.geom)) INTO distancia FROM objeto AS obj_1, objeto AS obj_2 WHERE obj_1.id = id_obj_1 AND obj_2.id = id_obj_2;	
		RETURN 200 - distancia;
	END IF;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION gad_actualizar_pivotes()
RETURNS VOID AS
$$
DECLARE
	cant_elementos bigint;
	cant_pivotes bigint;
	pivotes bigint;
	id_obj bigint;
	orden_maximo integer;
	nombre varchar;
	firma_obj firma;
BEGIN
	SELECT count(*) INTO cant_elementos FROM objeto;
	SELECT round(log(2,cant_elementos)) INTO cant_pivotes;
	SELECT count(*) INTO pivotes FROM pivote;
	IF cant_pivotes > pivotes THEN
		FOR i IN pivotes..(cant_pivotes - 1) LOOP
			SELECT id INTO id_obj FROM objeto WHERE id NOT IN (SELECT "idObjeto" FROM pivote) ORDER BY RANDOM() LIMIT 1;
			SELECT max(orden) INTO orden_maximo FROM pivote;
			IF orden_maximo IS NULL THEN
				orden_maximo = 0;
			END IF;
			INSERT INTO pivote VALUES (orden_maximo + 1, id_obj);
			nombre := CONCAT('P', orden_maximo + 1);
			EXECUTE 'ALTER TABLE firma ADD ' || nombre || ' real';

			FOR firma_obj IN (SELECT * FROM firma) LOOP
				EXECUTE 'UPDATE firma SET ' || nombre || ' = gad_distancia(' || id_obj ||', ' || firma_obj."idObjeto" || ') WHERE "idObjeto" = ' || firma_obj."idObjeto";
			END LOOP;
		END LOOP;
	END IF;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION gad_actualizar_indice(id_img bigint)
RETURNS VOID AS
$$
DECLARE
	obj objeto;
	piv pivote;
	nombre varchar;
BEGIN
	PERFORM gad_actualizar_pivotes();
	FOR obj IN (SELECT * FROM objeto WHERE imagen_id = id_img) LOOP
		INSERT INTO firma ("idObjeto") VALUES (obj.id);
		FOR piv IN (SELECT * FROM pivote ORDER BY orden ASC) LOOP
			nombre := CONCAT('P', piv.orden);
			EXECUTE 'UPDATE firma SET ' || nombre || ' = gad_distancia(' || piv."idObjeto" || ', ' || obj.id || ') WHERE "idObjeto" = ' || obj.id;
		END LOOP;
	END LOOP;	
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION gad_actualizar_datos() 
RETURNS BOOLEAN AS
$$
DECLARE
	obj objeto;
	id_img bigint;
BEGIN
	SELECT max(id) INTO id_img FROM imagen;
	FOR obj IN (SELECT * FROM objeto WHERE imagen_id = id_img) LOOP
		PERFORM gad_crear_geom(obj.id);
	END LOOP;
	PERFORM gad_actualizar_indice(id_img);
	RETURN true;
END;
$$
LANGUAGE plpgsql;

/*Funciones para la consulta*/

CREATE OR REPLACE FUNCTION gad_distancia_consulta(id_obj_1 bigint, id_obj_2 bigint)
RETURNS real AS
$$
DECLARE
	distancia real;
BEGIN
	SELECT st_area(st_intersection(obj_1.geom, obj_2.geom)) INTO distancia FROM objetoconsulta AS obj_1, objeto AS obj_2 WHERE obj_1.id = id_obj_1 AND obj_2.id = id_obj_2;	
	RETURN 200 - distancia;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION gad_similares(id_obj bigint) 
RETURNS TEXT AS
$$
DECLARE
	piv pivote;
	distancia real;
	nombre varchar;
	consulta text;
BEGIN
	consulta := 'select imagen.id, "idObjeto", gad_distancia_consulta(' || id_obj || ', "idObjeto") FROM imagen, firma, objeto WHERE objeto.imagen_id = imagen.id AND objeto.id = firma."idObjeto"';
	FOR piv IN (SELECT * FROM pivote) LOOP
		distancia := gad_distancia_consulta(id_obj, piv."idObjeto");
		nombre := CONCAT('P', piv.orden);
		consulta := CONCAT (consulta, ' AND ' || nombre || ' BETWEEN ' || (distancia - 50) || ' AND ' || (distancia + 50));
	END LOOP;
	EXECUTE 'INSERT INTO resultadoparcial ' || consulta;
	RETURN 'Listo';
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION gad_reducir_resultados() 
RETURNS text AS
$$
DECLARE
	res resultadoparcial;
	resaux resultadoparcial;
BEGIN
	FOR res IN (SELECT * FROM resultadoparcial) LOOP
		FOR resaux IN (SELECT * FROM resultadoparcial WHERE idobjeto = res.idobjeto) LOOP
			IF res.distancia < resaux.distancia THEN
				DELETE FROM resultadoparcial WHERE idimagen = resaux.idimagen AND idobjeto = resaux.idobjeto AND distancia = resaux.distancia;
			ELSE
				IF res.distancia > resaux.distancia THEN
					DELETE FROM resultadoparcial WHERE idimagen = res.idimagen AND idobjeto = res.idobjeto AND distancia = res.distancia;
				END IF;
			END IF;
		END LOOP;
	END LOOP;
	RETURN 'Listo';
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION gad_completar_distancias() 
RETURNS text AS
$$
DECLARE
	res resultado;
	objconsulta integer;
	objs integer;
	disttot real;
	porcentaje real;
BEGIN
	SELECT count(*) INTO objconsulta FROM objetoconsulta;
	FOR res IN (SELECT * FROM resultado) LOOP
		SELECT count(*) INTO objs FROM objeto WHERE imagen_id = res.idimagen;
		IF objconsulta >= objs THEN
			IF res.distancia <= 0 THEN
				EXECUTE 'UPDATE resultado SET distancia = ' || 100 || ' WHERE idimagen = ' || res.idimagen;
			ELSE
				disttot := (200 * (objconsulta - objs)) + res.distancia;
				porcentaje := disttot * 100 / (200 * objconsulta);
				EXECUTE 'UPDATE resultado SET distancia = ' || 100 - porcentaje || ' WHERE idimagen = ' || res.idimagen;
			END IF;
		ELSE
			IF objs > objconsulta THEN
				disttot := (200 * (objs - objconsulta)) + res.distancia;
				porcentaje := disttot * 100 / (200 * objs);
				EXECUTE 'UPDATE resultado SET distancia = ' || 100 - porcentaje || ' WHERE idimagen = ' || res.idimagen;
			END IF;
		END IF;
	END LOOP;
	RETURN 'Listo';
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION gad_crear_geom_consulta (id_obj bigint)
RETURNS VOID AS
$$
DECLARE
	g text;
	pto puntoconsulta;
	aux puntoconsulta;
	poligono geometry;
	poligono_nuevo geometry;
	escala real;
	mbb geometry;
	centro geometry;
	area double precision;
BEGIN
	g = 'POLYGON((';
	SELECT * INTO aux FROM puntoconsulta WHERE objeto_id = id_obj ORDER BY id ASC LIMIT 1;
	FOR pto IN (SELECT * FROM puntoconsulta WHERE objeto_id = id_obj) LOOP
		g = CONCAT(g, pto.x, ' ', pto.y, ',');
	END LOOP;
	g = CONCAT(g, aux.x, ' ', aux.y, '))');
	UPDATE objetoconsulta SET geom = st_geometryfromtext(g,0) WHERE id = id_obj;

	SELECT st_area(geom) INTO area FROM objetoconsulta WHERE id = id_obj;
	IF area > 0 THEN
		SELECT sqrt(200 / st_area(geom)) INTO escala FROM objetoconsulta WHERE id = id_obj;
		SELECT st_scale(geom, escala, escala) INTO poligono FROM objetoconsulta WHERE id = id_obj;
		SELECT st_centroid(box2d(poligono)) INTO centro;
		UPDATE objetoconsulta SET geom = st_translate(poligono,0 - st_x(centro),0 - st_y(centro)) WHERE id = id_obj;
	END IF;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION gad_rotar_180 (id_obj bigint)
RETURNS VOID AS
$$
DECLARE
	poligono geometry;
BEGIN
	SELECT geom INTO poligono FROM objetoconsulta WHERE id = id_obj;
	UPDATE objetoconsulta SET geom = st_rotate(poligono, pi()) WHERE id = id_obj;
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION gad_buscar_imagen() 
RETURNS SETOF record AS
$$
DECLARE
	obj objetoconsulta;
	res record;
BEGIN
	EXECUTE 'delete from resultadoparcial';
	EXECUTE 'delete from resultado';
	
	FOR obj IN (SELECT * FROM objetoconsulta) LOOP
		PERFORM gad_crear_geom_consulta (obj.id);
		PERFORM gad_similares(obj.id);
	END LOOP;
	FOR obj IN (SELECT * FROM objetoconsulta) LOOP
		PERFORM gad_rotar_180(obj.id);
		PERFORM gad_similares(obj.id);
	END LOOP;
	PERFORM gad_reducir_resultados();
	EXECUTE 'INSERT INTO resultado select idimagen, sum(distancia), count(idobjeto) FROM (SELECT distinct * from resultadoparcial) as res GROUP BY idimagen';
	PERFORM gad_completar_distancias();

	FOR res IN (SELECT resultado.idimagen, imagen.nombre, imagen.codxml, resultado.cantobjeto, resultado.distancia FROM resultado, imagen WHERE resultado.idimagen = imagen.id ORDER BY distancia DESC LIMIT 10) LOOP
		RETURN NEXT res;
	END LOOP;
	
	EXECUTE 'delete from puntoconsulta;';
	EXECUTE 'delete from objetoconsulta;';
	EXECUTE 'delete from imagenconsulta;';
END;
$$
LANGUAGE plpgsql;

/*Funcion de estadisticas*/

CREATE TYPE TDatos AS (
imagen bigint, 
pivotes bigint, 
tiempo real
);

CREATE OR REPLACE FUNCTION gad_datos() 
RETURNS TDatos AS
$$
DECLARE
	img bigint;
	tiem real;
	piv bigint;
	valores TDatos;
BEGIN
	SELECT count(*) INTO img FROM imagen;
	SELECT count(*) INTO piv FROM pivote;
	SELECT avg(tiempo) INTO tiem FROM tiempo;
	valores.imagen := img;
	valores.pivotes := piv;
	valores.tiempo := tiem;
	RETURN valores;
END;
$$
LANGUAGE plpgsql;
